from 组件.sqlite import SimpleSQLite
from datetime import datetime
import time
from typing import Dict, Any

##保存订单,弃用
def ensure_trade_stats_table():
    """确保当日订单统计表存在"""
    with SimpleSQLite("../组件/data/stock.db") as db:
        db.execute(
            """
            CREATE TABLE IF NOT EXISTS daily_trade_stats (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                date TEXT NOT NULL,
                stock_code TEXT NOT NULL,
                buy_price REAL NOT NULL,
                sell_price REAL NOT NULL,
                volume INTEGER NOT NULL,
                success INTEGER NOT NULL,
                profit REAL NOT NULL,
                buy_time TEXT NOT NULL,
                sell_time TEXT NOT NULL,
                order_sysid TEXT,
                high_5m REAL,
                low_5m REAL,
                last_5m REAL,
                points_5m INTEGER,
                remark TEXT,
                created_at TEXT NOT NULL
            )
            """
        )
        db.execute(
            """
            CREATE INDEX IF NOT EXISTS idx_daily_trade_stats_date_code
            ON daily_trade_stats(date, stock_code)
            """
        )
        # 迁移：老表增加列与唯一索引（若已存在将忽略异常）
        try:
            db.execute("ALTER TABLE daily_trade_stats ADD COLUMN order_sysid TEXT")
        except Exception:
            pass
        db.execute(
            """
            CREATE UNIQUE INDEX IF NOT EXISTS idx_dts_order_sysid
            ON daily_trade_stats(order_sysid)
            """
        )
def add_trade_stat(record: Dict[str, Any]) -> int:
    """插入一条当日订单统计记录

    预期字段：
    - date, stock_code, buy_price, sell_price, volume, success, profit,
      buy_time, sell_time, high_5m, low_5m, last_5m, points_5m, remark
    """
    with SimpleSQLite("../组件/data/stock.db") as db:
        # 去重：若提供了order_sysid，先检查是否已存在
        order_sysid_val = record.get("order_sysid")
        if order_sysid_val:
            exists = db.fetch_one(
                "SELECT id FROM daily_trade_stats WHERE order_sysid = ? LIMIT 1",
                (str(order_sysid_val),)
            )
            if exists and exists.get("id"):
                return int(exists["id"])  # 已存在直接返回ID
        raw_profit = record.get("profit", 0.0)
        try:
            profit_2 = round(float(raw_profit), 2)
        except Exception:
            profit_2 = 0.0

        data = {
            "date": record.get("date"),
            "stock_code": record.get("stock_code"),
            "buy_price": float(record.get("buy_price", 0.0)),
            "sell_price": float(record.get("sell_price", 0.0)),
            "volume": int(record.get("volume", 0)),
            "success": 1 if record.get("success") else 0,
            "profit": profit_2,
            "buy_time": record.get("buy_time"),
            "sell_time": record.get("sell_time"),
            "order_sysid": str(order_sysid_val) if order_sysid_val else None,
            "high_5m": record.get("high_5m"),
            "low_5m": record.get("low_5m"),
            "last_5m": record.get("last_5m"),
            "points_5m": record.get("points_5m"),
            "remark": record.get("remark", ""),
            "created_at": datetime.fromtimestamp(time.time()).strftime("%Y-%m-%d %H:%M:%S"),
        }
        return db.insert("daily_trade_stats", data)

##波动数据
def add_updown(stock_code,min_p,max_p,current_time):
    with SimpleSQLite("../组件/data/stock.db") as db:
        # 插入数据
        data_id = db.insert("stock_updown", {
            "stock_code": stock_code,
            "date": datetime.fromtimestamp(current_time).strftime("%Y-%m-%d"),
            "min_p": min_p,
            "max_p": max_p,
            "start_time": datetime.fromtimestamp(current_time).strftime("%Y-%m-%d %H:%M:%S"),
            "end_time": "",
            "longs": ""
        })
        return data_id
def update_updown(updown_id, current_time):
    with SimpleSQLite("../组件/data/stock.db") as db:
        data = db.fetch_one("SELECT * FROM stock_updown WHERE id = ?", (updown_id,))
        if data:
            start_time_str = data["start_time"]
            # 将字符串时间转换为datetime对象
            start_time = datetime.strptime(start_time_str, "%Y-%m-%d %H:%M:%S")
            # 计算持续时间（秒）
            duration = current_time - start_time.timestamp()
            end_time = datetime.fromtimestamp(current_time).strftime("%Y-%m-%d %H:%M:%S")
            db.update("stock_updown", {"end_time": end_time, "longs": duration}, "id = ?", (updown_id,))

##股票实时数据
def update_stock_data(stock_code, **kwargs):
    """
    统一更新股票数据的方法
    参数:
        stock_code: 股票代码
        **kwargs: 要更新的字段和值，支持以下字段:
            - updown_id: 波动ID
            - now_price: 当前价格
            - macd: MACD状态
            - rsi: RSI值
    """
    with SimpleSQLite("../组件/data/stock.db") as db:
        update_data = {}
        
        # 处理updown_id
        if 'updown_id' in kwargs:
            update_data['is_updown'] = kwargs['updown_id']
            update_data['updown_id'] = kwargs['updown_id']
        
        # 处理价格和时间
        if 'now_price' in kwargs:
            current_time = time.time()
            update_at = datetime.fromtimestamp(current_time).strftime("%Y-%m-%d %H:%M:%S")
            update_data['now_price'] = kwargs['now_price']
            update_data['update_at'] = update_at
        
        # 处理MACD和RSI
        if 'macd' in kwargs or 'rsi' in kwargs:
            if 'macd' in kwargs:
                update_data['macd'] = kwargs['macd']
            if 'rsi' in kwargs and kwargs['rsi'] is not None:
                update_data['rs1'] = f"{kwargs['rsi']:.2f}"

        if 'diefu' in kwargs:
            update_data['diefu'] = f"{kwargs['diefu']:.2f}"
        
        # 执行更新
        if update_data:
            db.update("stock", update_data, "stock_code = ?", (stock_code,))
def get_stock(stock_code):
    with SimpleSQLite("../组件/data/stock.db") as db:
        data = db.fetch_one("SELECT * FROM stock WHERE stock_code = ?", (stock_code,))
        return data
def get_stock_all():
    try:
        with SimpleSQLite("../组件/data/stock.db") as db:
            # 1. 获取所有 stock 数据
            data = db.fetch_all("SELECT * FROM stock")
            if not data:
                return []

            # 2. 提取所有有效的 updown_id
            updown_ids = [item["updown_id"] for item in data
                          if item.get("updown_id") is not None and item["updown_id"] > 0]

            # 3. 批量查询关联的 updown 数据
            updown_data = {}
            if updown_ids:
                placeholders = ",".join(["?"] * len(updown_ids))
                # 将参数显式转换为元组，避免sqlite3对列表绑定数量解析异常
                rows = db.fetch_all(
                    f"SELECT id, start_time, end_time FROM stock_updown WHERE id IN ({placeholders})",
                    tuple(updown_ids)
                )
                current_time = datetime.now()  # 获取当前时间
                for row in rows:
                    # print(f"id={row["id"]},time={row["start_time"]}")
                    start_time = datetime.strptime(row["start_time"], '%Y-%m-%d %H:%M:%S')  # 解析时间字符串
                    time_diff = current_time - start_time  # 计算时间差
                    minutes_diff = int(time_diff.total_seconds() / 60)  # 转换为分钟数
                    updown_data[row["id"]] = minutes_diff  # 存储分钟数

            # 4. 合并数据
            for item in data:
                item["longs"] = updown_data.get(item.get("updown_id"))  # 关联分钟数
                # item["rs1"] = f"{item["rs1"]:.0f}"

            return data
    except Exception as e:
        print(f"数据库操作失败: {e}")
        return []
##手动停止运行买入卖出
def change_run(stock_code,run):
    with SimpleSQLite("../组件/data/stock.db") as db:
        update_data = {}
        update_data['run'] = run
        # 执行更新
        if update_data:
            db.update("stock", update_data, "stock_code = ?", (stock_code,))


##通知
def need_notice(order_id,msg):
    with SimpleSQLite("../组件/data/stock.db") as db:
        ##data = db.fetch_one("SELECT * FROM order_notice WHERE order_id = ? and msg = ?", (order_id,msg))
        data = db.fetch_one("SELECT * FROM order_notice WHERE order_id = ?", (order_id,))
        if data:
            return data["notice"]
        else:
            return 0
def add_notice(order_id,msg):
    if need_notice(order_id,msg)==0:
        with SimpleSQLite("../组件/data/stock.db") as db:
            # 插入数据
            data_id = db.insert("order_notice", {
                "order_id": order_id,
                "notice": 1,
                "msg": msg,
            })
            return data_id
    else:
        with SimpleSQLite("../组件/data/stock.db") as db:
            db.update("order_notice",
                      {"msg": msg}, "order_id = ?", (order_id,))
def update_notice(order_id,msg):
    with SimpleSQLite("../组件/data/stock.db") as db:
        db.update("order_notice",
                  {"notice": 2,"msg": msg}, "order_id = ?", (order_id,))



def main():
    data = []
    print(data)

if __name__ == "__main__":
    main()